﻿#region Creditos

/* Autor: Edilberto Sánchez Forero	*/
/* Email: esanchezfo@gmail.com		*/
/* Licencia: LGPL, Software Libre	*/
/* País: Colombia, Bogotá			*/

#endregion

#region Librerias

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Reflection;
using System.Text;
using System.Text.RegularExpressions;
using System.Xml;

using ESF.General;
using ESF.General.Text;
using ESF.General.Entity;
using System.Collections.Specialized;
using System.Diagnostics;

#endregion


namespace ESF.Data
{
	/// <summary>
	/// Contiene las funciones de recuperacion de informacion de la base de datos
	/// </summary>
	public class DbCommon
	{	
		#region Miembros
		
		private ConnectionStringSettings _ConnSettings;
		
		private DbProviderFactory _DbFactory;

		private const int DEFAULTCOMMANDTIMEOUTSECONDS = 60;
		
		public bool TransformParametersQuestionMark;
		public bool AllowEmptyStrings;
		
		protected string _ParameterSeparator;
		
		public string [] ConnectionStringParams;
		
		#endregion
	
	
		#region Constructor
		
		protected DbCommon () 
		{
		    AllowEmptyStrings = false;
		    TransformParametersQuestionMark = false;
		}


		protected DbCommon (ConnectionStringSettings pSettings, params string [] pParams) : this ()
		{
			_ConnSettings = pSettings;

			_DbFactory = DbProviderFactories.GetFactory (_ConnSettings.ProviderName);

			ConnectionStringParams = pParams;

			switch (ProviderName)
			{
				case "System.Data.SqlClient":
				case "System.Data.OleDb":
				case "System.Data.SQLite":
					_ParameterSeparator = "@";
					break;

				default: 
					_ParameterSeparator = string.Empty;
					return;
			}
		}
		
		
		protected DbCommon (string pConnectionName) : this (ReadConfig.GetConnectionSetting (pConnectionName)) {}


		protected DbCommon (string pConnectionName, params string [] pParams) : this (ReadConfig.GetConnectionSetting (pConnectionName), pParams) {}
		
		#endregion
	
	
		#region Propiedades
		
		public string ConnectionName { get { return _ConnSettings.Name; } }
		
		public string ProviderName { get { return _ConnSettings.ProviderName; } }
		
		public string ConnectionString { get { return string.Format (_ConnSettings.ConnectionString, ConnectionStringParams); } }

		#endregion
	
	
		#region Delegados
		
		public delegate void AddExtraParameters (IDbCommand pCommand);
		
		#endregion
		

		#region Log

		protected void StartExec (StringBuilder pstbLog, Stopwatch pSWatch)
		{
			if (Log.IsDebugLogEnabled)
			{
				if (pstbLog == null)
				{
					pstbLog = new StringBuilder ();
				}

				if (pSWatch == null)
				{
					pSWatch = new Stopwatch ();
				}

				pSWatch.Start();

				pstbLog.AppendLine(new String('-', 80));
				pstbLog.AppendFormat("Executing Start at {0}", DateTime.Now);
				pstbLog.AppendLine();
			}
		}


		protected void RegCmdExec (StringBuilder pstbLog, IDbCommand pCmd)
		{
			if (Log.IsDebugLogEnabled)
			{
				pstbLog.AppendLine (ToString (pCmd));
				pstbLog.AppendLine ();
			}
		}


		protected void EndExec(StringBuilder pstbLog, Stopwatch pSWatch)
		{
			StackTrace st;


			if (Log.IsDebugLogEnabled)
			{
				st = new StackTrace ();

				pSWatch.Stop ();

				pstbLog.AppendFormat("Completed at {0} in {1}", DateTime.Now, TextHelper.ToStringMS (pSWatch.ElapsedMilliseconds));
				pstbLog.AppendLine();
				pstbLog.AppendLine(new String('-', 80)); 


				pstbLog.AppendLine ("Call Stacktrace:");

				foreach (StackFrame fr in st.GetFrames ())
				{
					pstbLog.AppendFormat ("{0}: {1}", fr.GetMethod ().Module, fr.GetMethod ().Name);
					pstbLog.AppendLine ();
				}

				pstbLog.AppendLine(new String('-', 80)); 

				Log.WriteLineDataBase (pstbLog.ToString ());
			}
		}

		#endregion

		
		#region Comandos

		#region Command Basic
		
		public IDbCommand CreateCommand ()
		{
			return CreateCommand (DEFAULTCOMMANDTIMEOUTSECONDS);
		}


		public IDbCommand CreateCommandWithoutTimeout ()
		{
			return CreateCommand (0);
		}
		
		
		public IDbCommand CreateCommand (int pCommandTimeoutSeconds)
		{
			return CreateCommand (string.Empty, pCommandTimeoutSeconds);
		}
		
		
		public IDbCommand CreateSPCommand ()
		{
			return CreateSPCommand (DEFAULTCOMMANDTIMEOUTSECONDS);
		}


		public IDbCommand CreateSPCommandWithoutTimeout ()
		{
			return CreateSPCommand (0);
		}
		
		
		public IDbCommand CreateSPCommand (int pCommandTimeoutSeconds)
		{
			return CreateSPCommand (string.Empty, pCommandTimeoutSeconds);
		}

		#endregion
		

		public IDbCommand CreateCommand (string pCommandSQL)
		{
			return CreateCommand (pCommandSQL, DEFAULTCOMMANDTIMEOUTSECONDS);
		}


		public IDbCommand CreateCommandWithoutTimeout (string pCommandSQL)
		{
			return CreateCommand (pCommandSQL, 0);
		}

		
		public IDbCommand CreateCommand (string pCommandSQL, int pCommandTimeoutSeconds)
		{
			return CreateCommand (pCommandSQL, CommandType.Text, pCommandTimeoutSeconds);
		}
		
		
		public IDbCommand CreateSPCommand (string pCommandSQL)
		{
			return CreateSPCommand (pCommandSQL, DEFAULTCOMMANDTIMEOUTSECONDS);
		}


		public IDbCommand CreateSPCommandWithoutTimeout (string pCommandSQL)
		{
			return CreateSPCommand (pCommandSQL, 0);
		}

		
		public IDbCommand CreateSPCommand (string pCommandSQL, int pCommandTimeoutSeconds)
		{
			return CreateCommand (pCommandSQL, CommandType.StoredProcedure, pCommandTimeoutSeconds);
		}
		

		private IDbCommand CreateCommand (string pCommandSQL, CommandType pobjCommandType, int pCommandTimeoutSeconds)
		{
			IDbCommand dbcCommand;
			
			
			dbcCommand = _DbFactory.CreateCommand ();
			
			dbcCommand.CommandType = pobjCommandType;
			dbcCommand.CommandText = pCommandSQL;			
			dbcCommand.CommandTimeout = pCommandTimeoutSeconds;
			
			return dbcCommand;
		}
		
		
		public IDbConnection CreateConnection ()
		{	
			IDbConnection cnn;
			
			
			cnn = _DbFactory.CreateConnection ();
			
			cnn.ConnectionString = ConnectionString;
			
			return cnn;
		}
		
		
		protected IDbDataAdapter CreateDataAdapter ()
		{
			return _DbFactory.CreateDataAdapter ();
		}
		
		#endregion
		
		
		#region Parametros ID
		
		public void AddIDParameter (IDbCommand pCommand, string pName, int? pValue)
		{
			if (pValue == null) { AddNullParameter (pCommand, pName); return; }
		
			AddIDParameter (pCommand, pName, pValue.Value);
		}
		
		
		public void AddIDParameter (IDbCommand pCommand, string pName, IBasicReference pValue)
		{
			if (pValue == null) { AddNullParameter (pCommand, pName); return; }
			
			AddIDParameter (pCommand, pName, pValue.ID);
		}
		
		
		public void AddNameParameter (IDbCommand pCommand, string pName, BasicReference pValue)
		{
			AddNameParameter (pCommand, pName, pValue, ComparisonTypes.Equals);
		}


		public void AddNameParameter (IDbCommand pCommand, string pName, BasicReference pValue, ComparisonTypes pComparisonType)
		{
			if (pValue == null) { AddNullParameter (pCommand, pName); return; }
			
			AddParameter (pCommand, pName, pValue.Name, pComparisonType);
		}


		public void AddTRUEFALSEParameter (IDbCommand pCommand, string pName, bool? pValue)
		{
			AddTextBoolParameter (pCommand, pName, pValue, "TRUE", "FALSE");
		}


		public void AddYESNOParameter (IDbCommand pCommand, string pName, bool? pValue)
		{
			AddTextBoolParameter (pCommand, pName, pValue, "YES", "NO");
		}


		public void AddSINOParameter (IDbCommand pCommand, string pName, bool? pValue)
		{
			AddTextBoolParameter (pCommand, pName, pValue, "SI", "NO");
		}


		public void AddTextBoolParameter (IDbCommand pCommand, string pName, bool? pValue, string pTRUEText, string pFALSEText)
		{
			if (pValue == null) { AddNullParameter (pCommand, pName); return; }
			
			AddParameter (pCommand, pName, (pValue.Value ? pTRUEText : pFALSEText));
		}
		
		
		public void AddCodeParameter (IDbCommand pCommand, string pName, BasicReference pValue)
		{
			if (pValue == null) { AddNullParameter (pCommand, pName); return; }
			
			AddParameter (pCommand, pName, pValue.Code);
		}
		
		
		public void AddIDParameter (IDbCommand pCommand, string pName, int pValue)
		{
			if (pValue == 0) { AddNullParameter (pCommand, pName); return; }
		
			AddParameter (pCommand, pName, pValue);
		}
		
		
		public void AddIDParameter (IDbCommand pCommand, string pName, long? pValue)
		{
			if (pValue == null) { AddNullParameter (pCommand, pName); return; }
		
			AddIDParameter (pCommand, pName, pValue.Value);
		}
		
		
		public void AddIDParameter (IDbCommand pCommand, string pName, long pValue)
		{
			if (pValue == 0) { AddNullParameter (pCommand, pName); return; }
		
			AddParameter (pCommand, pName, pValue);
		}
		
		#endregion
		
		
		#region Parametros Fecha

		public void AddINIDsParameter (IDbCommand pCmd, string pName, bool pAddINText, List<BasicReference> pItems)
		{
			List<int?> IDs;


			if (DataHelper.IsEmpty (pItems)) { AddNullParameter (pCmd, pName); return; }

			IDs = new List<int?> (pItems.Count);

			foreach (BasicReference item in pItems)
			{
				IDs.Add (item.ID);
			}

			AddINParameter (pCmd, pName, pAddINText, IDs.ToArray ());
		}


		public void AddINTypeIDsParameter (IDbCommand pCmd, string pName, bool pAddINText, List<BasicReference> pItems)
		{
			List<int?> IDs;


			if (DataHelper.IsEmpty (pItems)) { AddNullParameter (pCmd, pName); return; }

			IDs = new List<int?> (pItems.Count);

			foreach (BasicReference item in pItems)
			{
				IDs.Add (item.BasicReferenceType.ID);
			}

			AddINParameter (pCmd, pName, pAddINText, IDs.ToArray ());
		}


		public void AddINCodesParameter (IDbCommand pCmd, string pName, bool pAddINText, List<BasicReference> pItems)
		{
			List<string> codes;


			if (DataHelper.IsEmpty (pItems)) { AddNullParameter (pCmd, pName); return; }

			codes = new List<string> (pItems.Count);

			foreach (BasicReference item in pItems)
			{
				codes.Add (item.Code);
			}

			AddINParameter (pCmd, pName, pAddINText, codes);
		}


		public void AddINParameter (IDbCommand pCmd, string pName, bool pAddINText, params byte [] pValues)
		{
			AddParameter (pCmd, pName, TextHelper.GenerateINIDs (pAddINText, pValues));
		}
		
		
		public void AddINParameter (IDbCommand pCmd, string pName, bool pAddINText, params ushort [] pValues)
		{
			AddParameter (pCmd, pName, TextHelper.GenerateINIDs (pAddINText, pValues));
		}
		
		
		public void AddINParameter (IDbCommand pCmd, string pName, bool pAddINText, params long [] pValues)
		{
			AddParameter (pCmd, pName, TextHelper.GenerateINIDs (pAddINText, pValues));
		}
		
		
		public void AddINParameter (IDbCommand pCmd, string pName, bool pAddINText, params int [] pValues)
		{
			AddParameter (pCmd, pName, TextHelper.GenerateINIDs (pAddINText, pValues));
		}


		public void AddINParameter (IDbCommand pCmd, string pName, bool pAddINText, params int? [] pValues)
		{
			AddParameter (pCmd, pName, TextHelper.GenerateINIDs (pAddINText, pValues));
		}
		
		
		public void AddINParameter (IDbCommand pCmd, string pName, bool pAddINText, params string [] pValues)
		{
			AddParameter (pCmd, pName, TextHelper.GenerateIN (pAddINText, pValues));
		}


		public void AddINParameter (IDbCommand pCmd, string pName, bool pAddINText, List<string> pValues)
		{
			if (!DataHelper.IsEmpty (pValues))
			{
				AddINParameter (pCmd, pName, false, pValues.ToArray ());
			}
		}


		public void AddINParameter (IDbCommand pCmd, string pName, bool pAddINText, List<int> pValues)
		{
			if (!DataHelper.IsEmpty (pValues))
			{
				AddINParameter (pCmd, pName, false, pValues.ToArray ());
			}
		}


		public void AddINParameter (IDbCommand pCmd, string pName, bool pAddComilla, bool pAddINText, params string [] pValues)
		{
			AddParameter (pCmd, pName, TextHelper.GenerateIN (pAddComilla, pValues));
		}
		

		public  void AddDateParameter (IDbCommand pCommand, string pName, DateTime? dtmValue)
		{
			if (dtmValue == null) { AddNullParameter (pCommand, pName); return; }
		
			AddParameter (pCommand, pName, dtmValue.Value);
		}


		public  void AddInitialDateParameter (IDbCommand pCommand, string pName, DateTime? dtmValue)
		{
			if (dtmValue == null) { AddNullParameter (pCommand, pName); return; }
		
			AddParameter (pCommand, pName, DataHelper.SetAsStartDate (dtmValue));
		}


		public  void AddFinalDateParameter (IDbCommand pCommand, string pName, DateTime? dtmValue)
		{
			if (dtmValue == null) { AddParameter (pCommand, pName, null); return; }
		
			AddParameter (pCommand, pName, DataHelper.SetAsEndDate (dtmValue));
		}
		
		
		public void AddPublicPropertyAsStringParams (IDbCommand pCommand, object pObject)
		{
			AddPublicPropertyAsStringParams (pCommand, pObject, string.Empty, true);
		}
		
		
		public void AddPublicPropertyAsStringParams (IDbCommand pCommand, object pObject, string pParamPrefix, bool pExcludeNulls)
		{
			PropertyInfo[] arrProperties;
			
			object value;
			
			
			if (pObject == null) { return; }
			
			arrProperties = pObject.GetType().GetProperties();

            foreach (PropertyInfo property in arrProperties)
            {
            	value = property.GetValue (pObject, null);
            	
            	if (value == null)
            	{
            		if (!pExcludeNulls)
            		{
            			AddNullParameter (pCommand, string.Format ("{0}{1}", pParamPrefix, property.Name));
            		}            		
            	}
            	else if (TextHelper.IsEmpty (value.ToString ()) && !AllowEmptyStrings)
            	{
            		AddNullParameter (pCommand, string.Format ("{0}{1}", pParamPrefix, property.Name));
            	}
            	else
            	{
            		AddParameter (pCommand, string.Format ("{0}{1}", pParamPrefix, property.Name), value);
            	}            	
            }
		}

		#endregion
			
		
		#region Parametros

		public T GetParameterValue<T> (IDbCommand pCommand, string pParamName)
		{
			return GetParameterValue <T> (pCommand, pParamName, default (T));
		}


		public T GetParameterValue<T> (IDbCommand pCommand, string pParamName, T pDefaultValue)
		{
			object value;
			
			
			FormatParameterName (ref pParamName); 
			
			value = pCommand.Parameters [pParamName];
			
			if (value is IDbDataParameter)
			{
				value = ((IDbDataParameter) value).Value;
				
				if (value == null || value == DBNull.Value) 
				{
					return pDefaultValue;
				}
				
				if (value is T)
				{
					return (T) value;
				}
				else
				{
					throw new Exception (string.Format ("The type of parameter is {0} but required {1} is different", value.GetType (), typeof (T)));
				}
			}
			else
			{
				throw new Exception (string.Format ("Kind of parameter is not IDbDataParameter is {0}", value.GetType ()));
			}
		}


		public void FormatParameterName (ref string pName)
		{
			if (string.IsNullOrEmpty (_ParameterSeparator)) { return; }
		
			if (pName.StartsWith (_ParameterSeparator))
			{
				return;
			}
			
			pName = _ParameterSeparator + pName;
		}
		
		
		public void AddOutParameter (IDbCommand pCommand, string pName, DbType pDbType)
		{
			AddParameter (pCommand, pName, ParameterDirection.Output, pDbType);
		}
		
		
		public void AddInOutParameter (IDbCommand pCommand, string pName, object pValue)
		{
			AddParameter (pCommand, pName, pValue, ParameterDirection.InputOutput);
		}


		public void AddInOutIDParameter (IDbCommand pCommand, string pName, int? pValue)
		{
			if (pValue != null)
			{
				AddInOutIDParameter (pCommand, pName, pValue.Value);
			}
		}


		public void AddInOutIDParameter (IDbCommand pCommand, string pName, long? pValue)
		{
			if (pValue != null)
			{
				AddInOutIDParameter (pCommand, pName, pValue.Value);
			}
		}


		public void AddInOutIDParameter (IDbCommand pCommand, string pName, int pValue)
		{
			if (Validate.IsFilledID (pValue))
			{
				AddParameter (pCommand, pName, pValue, ParameterDirection.InputOutput);
			}
			else
			{
				AddNullParameter (pCommand, pName);
			}
		}


		public void AddInOutIDParameter (IDbCommand pCommand, string pName, long pValue)
		{
			if (Validate.IsFilledID (pValue))
			{
				AddParameter (pCommand, pName, pValue, ParameterDirection.InputOutput);
			}
			else
			{
				AddNullParameter (pCommand, pName);
			}
		}


		private void AddParameter (IDbCommand pCommand, string pName, ParameterDirection prdDireccion, DbType pDbType)
		{
			AddParameter (pCommand, pName, prdDireccion, pDbType, null);
		}
		
		
		private void AddParameter (IDbCommand pCommand, string pName, ParameterDirection prdDireccion, DbType pDbType, object pValue)
		{
		    IDbDataParameter dbpParameter;

	
		    dbpParameter = pCommand.CreateParameter ();

			FormatParameterName (ref pName);

			dbpParameter.ParameterName = pName;
		    dbpParameter.Direction = prdDireccion;
		    dbpParameter.Value = pValue;
		    dbpParameter.DbType = pDbType;
		    dbpParameter.Size = -1;
		    
		    if (pCommand.Parameters.Contains (pName))
		    {
				pCommand.Parameters [pName] = dbpParameter;
		    }
		    else
		    {
				pCommand.Parameters.Add (dbpParameter);
			}
		}
		
		
		public  void AddNullParameter (IDbCommand pCommand, string pName)
		{
			AddParameter (pCommand, pName, null);
		}


		public  void AddParameter (IDbCommand pCommand, string pName, Type objTipo)
		{
			AddParameter (pCommand, pName, objTipo, ParameterDirection.Input);
		}
		
		
		public void AddParametersPagging (IDbCommand pCommand, PaggingInfo pPagInfo)
		{
			if (pPagInfo == null) { return; }
			
			AddParameter (pCommand, "pPageIndex", pPagInfo.PageIndex);
			AddParameter (pCommand, "pPageSize", pPagInfo.PageSize);
		}
		
		
		public void AddAuditableEntityParamsCreation (IDbCommand pCmd, IAuditableEntity pAuditableEntity)
		{
			AddParameter (pCmd, "pCreationDateTime", DateTime.Now);
			AddParameter (pCmd, "pUserCreationAlias", pAuditableEntity.UserCreationAlias);
		}
		
		
		public void AddAuditableEntityParamsUpdate (IDbCommand pCmd, IAuditableEntity pAuditableEntity)
		{
			AddParameter (pCmd, "pLastUpdateDateTime", DateTime.Now);
			AddParameter (pCmd, "pUserLastUpdateAlias", pAuditableEntity.UserLastUpdateAlias);
		}


		public void AddParameters (IDbCommand pCmd, NameValueCollection pParameters)
		{
			if (pParameters == null) { return; }

			foreach (string pKey in pParameters.AllKeys)
			{
				AddParameter (pCmd, pKey, pParameters [pKey]);
			}
		}


		public void AddListParameter(IDbCommand pCommand, string pName, List<string> pValues, ComparisonTypes pComparisonType = ComparisonTypes.Equals, char pSeparator = ',')
		{
			if (DataHelper.IsEmpty (pValues))
			{
				AddNullParameter (pCommand, pName);
				return;
			}

			AddListParameter(pCommand, pName, pValues.ToArray(), pComparisonType, pSeparator);
		}


		public void AddListParameter(IDbCommand pCommand, string pName, string[] pValues, ComparisonTypes pComparisonType = ComparisonTypes.Equals, char pSeparator = ',')
		{
			StringBuilder resultValue;


			if (DataHelper.IsEmpty(pValues))
			{
				AddNullParameter(pCommand, pName);
				return;
			}

			resultValue = new StringBuilder ();

			if (pComparisonType == ComparisonTypes.Equals)
			{
				AddParameter (pCommand, pName, string.Join (pSeparator.ToString (), pValues));
				return;
			}

			foreach (string itemValue in pValues)
			{
				switch (pComparisonType)
				{
					case ComparisonTypes.LikeFull:
						resultValue.AppendFormat ("%{0}%", itemValue);
						break;

					case ComparisonTypes.LikeLeft:
						resultValue.AppendFormat ("%{0}", itemValue);
						break;

					case ComparisonTypes.LikeRight:
						resultValue.AppendFormat ("{0}%", itemValue);
						break;
				}

				resultValue.Append (pSeparator);
			}

			resultValue.Length --;

			AddParameter(pCommand, pName, resultValue.ToString ());
		}
		
		
		/// <summary>
		/// Genera un parametro con base en las propiedades proporcionadas
		/// </summary>
		/// <param name="pCommand">Comando pregenerado con CreateCommand</param>
		/// <param name="strName">Nombre del parametro</param>
		/// <param name="objValue">Value del parametro, de acuerdo con su tipo se ajusta el tipo del parametro</param>
		/// <returns>El parametro preparado con las propiedades dadas</returns>
		public  void AddParameter (IDbCommand pCommand, string pName, object objValue)
		{
			AddParameter (pCommand, pName, objValue, ParameterDirection.Input, ComparisonTypes.Equals);
		}
		
		
		public  void AddParameter (IDbCommand pCommand, string pName, object objValue, ComparisonTypes penuComparisonType)
		{
			AddParameter (pCommand, pName, objValue, ParameterDirection.Input, penuComparisonType);
		}

		
		
		public  void AddParameter (IDbCommand pCommand, string pName, object objValue, object objDefaultValue)
		{
			AddParameter (pCommand, pName, objValue, objDefaultValue, ComparisonTypes.Equals);
		}
		
		
		public  void AddParameter (IDbCommand pCommand, string pName, object objValue, ParameterDirection prdDireccion)
		{
			AddParameter (pCommand, pName, objValue, prdDireccion, ComparisonTypes.Equals);
		}
		
		
		public  void AddParameter (IDbCommand pCommand, string pName, object objValue, object objDefaultValue, ComparisonTypes penuComparisonType)
		{
			if (objValue == null) { objValue = objDefaultValue; }
			
			AddParameter (pCommand, pName, objValue, penuComparisonType);
		}
		
		
		/// <summary>
		/// Genera un parametro con base en las propiedades proporcionadas
		/// </summary>
		/// <param name="pCommand">Comando pregenerado con CreateCommand</param>
		/// <param name="strName">Nombre del parametro</param>
		/// <param name="objValue">Value del parametro, de acuerdo con su tipo se ajusta el tipo del parametro</param>
		/// <param name="prdDireccion">Direccion del parametro: Entrada, Salida o Ambas</param>
		/// <param name="intTamano">Tamaño maximo (si aplica) del parametro</param>
		/// <returns>El parametro preparado con las propiedades dadas</returns>
		public void AddParameter (IDbCommand pCommand, string pName, object pobjValue, ParameterDirection prdDireccion, ComparisonTypes penuComparisonType)
		{
			string strValue;
			
			DbType dbType;
		
			
			#region Ajuste del Tipo
			
			if (pobjValue is long || pobjValue is long?) { dbType = DbType.Int64; }

			else if (pobjValue is ulong || pobjValue is ulong?) { dbType = DbType.Int64; }


			else if (pobjValue is int || pobjValue is int?) { dbType = DbType.Int32; }

			else if (pobjValue is uint || pobjValue is uint?) { dbType = DbType.Int64; }


			else if (pobjValue is double || pobjValue is float || pobjValue is double? || pobjValue is float?) { dbType = DbType.Double; }

			else if (pobjValue is decimal || pobjValue is decimal?) { dbType = DbType.Decimal; }


			else if (pobjValue is byte || pobjValue is byte?) { dbType = DbType.Byte; }


			else if (pobjValue is ComparisonTypes) { dbType = DbType.Byte; }


			else if (pobjValue is short || pobjValue is short?) { dbType = DbType.Int16; }

			else if (pobjValue is ushort || pobjValue is ushort?) { dbType = DbType.Int32; }


			else if (pobjValue is DateTime || pobjValue is DateTime?) { dbType = DbType.DateTime; }


			else if (pobjValue is bool || pobjValue is bool?) { dbType = DbType.Boolean; }


			else if (pobjValue is byte[]) { dbType = DbType.Binary; }
			
			else { dbType = DbType.String; }
			
			#endregion
			
			
			if (pobjValue is XmlDocument) { pobjValue = ((XmlDocument) pobjValue).OuterXml; }
			else if (pobjValue is XmlAttribute) { pobjValue = ((XmlAttribute) pobjValue).Value; }
			else if (pobjValue is XmlNode) { pobjValue = ((XmlNode) pobjValue).OuterXml; }
			else if (pobjValue is ComparisonTypes) { pobjValue = Convert.ToByte (pobjValue); }

			else if (dbType == DbType.String)
			{
				strValue = Convert.ToString (pobjValue).Trim ();
			
				if (TextHelper.IsEmpty (strValue))
				{
					if (AllowEmptyStrings)
					{
						pobjValue = strValue;
					}
					else
					{
						pobjValue = DBNull.Value;
					}
				}
				else
				{
					switch (penuComparisonType)
					{
						case ComparisonTypes.LikeFull:
							strValue = "%" + strValue + "%"; 
							break;
							
						case ComparisonTypes.LikeLeft: 
							strValue = "%" + strValue; 
							break;
						
						case ComparisonTypes.LikeRight:
							strValue = strValue + "%";
							break;
					}
					
					pobjValue = strValue;
				}
			}
			
			AddParameter (pCommand, pName, prdDireccion, dbType, pobjValue);
		}


		public void RemoveParameter (IDbCommand pCmd, string pParameterName)
		{
			FormatParameterName (ref pParameterName);

			pCmd.Parameters.RemoveAt (pParameterName);
		}
		
		#endregion
		
		
		#region Ajustes de Parametros
		
		public void AddNotDefinedParametersAsNull (IDbCommand pCommand)
		{
			string strParamSep;
			
			
			Validate.FilledObject (true, "Command", pCommand);
			Validate.Filled (true, "Command Text", pCommand.CommandText);
			
			if (pCommand.CommandType != CommandType.Text) { return; }
			
			if (_ParameterSeparator == "@")
			{
				strParamSep = @"\" + _ParameterSeparator;
			}
			else
			{
				strParamSep = _ParameterSeparator;
			}
			
			foreach (Match objMatchRegEx in Regex.Matches (pCommand.CommandText, strParamSep + @"\w+"))
			{
				if (!pCommand.Parameters.Contains (objMatchRegEx.Value))
				{
					AddNullParameter (pCommand, objMatchRegEx.Value);
				}
			}
		}
		
		
		/// <summary>
		/// Se cambian los parametros del texto del comando a ? y se asignan parametros de relleno
		/// para coincidir con los nombres y ubicacion de los ?
		/// </summary>
		/// <param name="pCommand"></param>
		internal void AdjustParametersCommandQuestionMark (IDbCommand pCommand)
		{
			List<IDbDataParameter> lstParameters;
			IDbDataParameter parameter;
			
			Regex regex;
			
			int diferenciador;
			
			string strParamSep;
			
			
			if (!TransformParametersQuestionMark || pCommand.Parameters.Count == 0)
			{
				return;
			}
			
			
			lstParameters = new List<IDbDataParameter> (pCommand.Parameters.Count);
			
			foreach (IDbDataParameter param in pCommand.Parameters)
			{
				lstParameters.Add (param);
			}
						
			pCommand.Parameters.Clear ();
			
			diferenciador = 0;
			
			if (_ParameterSeparator == "@")
			{
				strParamSep = @"\" + _ParameterSeparator;
			}
			else
			{
				strParamSep = _ParameterSeparator;
			}
						
			regex = new Regex (strParamSep + @"[A-Za-z0-9]+");
			
			foreach (Match match in regex.Matches (pCommand.CommandText))
			{
				parameter = lstParameters.Find (p => string.Format ("{0}{1}", _ParameterSeparator, p.ParameterName) == match.Value);
				
				if (parameter == null)
				{
					throw new GenericException (string.Format ("El parametro {0} no fue proporcionado", match.Value));
				}
				
				AddParameter (pCommand, string.Format ("{0}_{1}", parameter.ParameterName, diferenciador), parameter.Value);
				
				diferenciador ++;
			}
			
			pCommand.CommandText = regex.Replace (pCommand.CommandText, "?");
		}
		
		#endregion
		
		
		#region De Uso general

		public static string ToString (IDbDataParameter pdbpParam)
		{
			return string.Format("{0} ({1}): {2}", pdbpParam.ParameterName, pdbpParam.DbType, GetSQLParamValue (pdbpParam));
		}
		
		
		public string ToString (IDbCommand pCommand)
		{
			return ToString (ProviderName, pCommand);
		}
		
		
		public static string ToString (string pstrProviderName, IDbCommand pCommand)
		{
			StringBuilder stbCommand;
				
		
			if (pCommand == null || 
				string.IsNullOrEmpty (pCommand.CommandText) || 
				string.IsNullOrEmpty (pstrProviderName)) 
			{ return string.Format ("{0}{1}{2}{1}{0}", 
				new String ('-', 200),
				Environment.NewLine, "Without Command or Provider"); }
			
			
			stbCommand = new StringBuilder ();
			
			stbCommand.AppendLine (new String ('-', 200));
			stbCommand.AppendFormat ("Provider: {0}", pstrProviderName);
			stbCommand.AppendLine ();
			
			if (pCommand.Connection != null)
			{
				stbCommand.AppendFormat ("Connection String: {0}", pCommand.Connection.ConnectionString);
				stbCommand.AppendLine ();
			}
			
			stbCommand.AppendFormat ("Text: {0}", pCommand.CommandText);
			stbCommand.AppendLine ();
			stbCommand.AppendFormat ("TimeOut: {0}s", pCommand.CommandTimeout);
			stbCommand.AppendLine ();
			stbCommand.AppendFormat ("Type: {0}", pCommand.CommandType);
			
			if (pCommand.CommandType == CommandType.StoredProcedure)
			{
				stbCommand.AppendLine ();
				stbCommand.AppendLine ();
				
				switch (pstrProviderName)
				{
					#region MSSQL

					case "System.Data.SqlClient":
						
						if (pCommand.Parameters.Count > 0)
						{
							foreach (IDbDataParameter dbpParam in pCommand.Parameters)
							{
								stbCommand.AppendFormat ("DECLARE {0} AS {1}", dbpParam.ParameterName, TraslateDbType (pstrProviderName, dbpParam.DbType));
								stbCommand.AppendLine ();
							}
							
							stbCommand.AppendLine ();
							
							foreach (IDbDataParameter dbpParam in pCommand.Parameters)
							{
								stbCommand.AppendFormat ("SET {0} = {1}", dbpParam.ParameterName, GetSQLParamValue (dbpParam));
								stbCommand.AppendLine ();
							}
						}
						
						stbCommand.AppendFormat ("EXEC {0} ", pCommand.CommandText);
								
						foreach (DbParameter dbpParam in pCommand.Parameters)
						{
							stbCommand.AppendFormat ("{0} = {0}{1}, ", dbpParam.ParameterName, 
								(dbpParam.Direction == ParameterDirection.Output || dbpParam.Direction == ParameterDirection.InputOutput ? " OUT" : string.Empty));
						}
						
						if (pCommand.Parameters.Count > 0)
						{
							stbCommand.Length -= 2;
						}
							
						break;

					#endregion
						
					#region MySQL
					
					case "MySql.Data.MySqlClient":
						
						if (pCommand.Parameters.Count > 0)
						{
							foreach (DbParameter dbpParam in pCommand.Parameters)
							{
								stbCommand.AppendFormat ("SET {0} = {1};", dbpParam.ParameterName, GetSQLParamValue (dbpParam));
								stbCommand.AppendLine ();
							}
						
							stbCommand.AppendLine ();
						}
						
						stbCommand.AppendFormat ("CALL {0} ", pCommand.CommandText);
						
						foreach (DbParameter dbpParam in pCommand.Parameters)
						{
							stbCommand.AppendFormat ("");
						}
						
						break;

					#endregion
						
					#region Default

					default:
					
						if (pCommand.Parameters.Count > 0)
						{
							foreach (DbParameter dbpParam in pCommand.Parameters)
							{
								stbCommand.AppendFormat("Parameter: {0} = {1};", dbpParam.ParameterName, GetSQLParamValue (dbpParam));
								stbCommand.AppendLine();
							}

							stbCommand.AppendLine();
						}

						stbCommand.AppendFormat("Command: {0} ", pCommand.CommandText);
						
						break;
				
					#endregion
				}
			}			
			
			stbCommand.AppendLine ();
			stbCommand.AppendLine (new String ('-', 200));
			
			return stbCommand.ToString ();
		}
		
		
		public static string TraslateDbType (string pProviderName, DbType pDbType)
		{
			switch (pProviderName)
			{
				case "System.Data.SqlClient":
					switch (pDbType)
					{
						case DbType.Boolean: return "BIT";
						case DbType.Byte: case DbType.SByte: return "TINYINT";
						case DbType.DateTime: case DbType.DateTime2: case DbType.DateTimeOffset: return "DATETIME";
						case DbType.Int16: case DbType.Int32: case DbType.UInt16: case DbType.UInt32: return "INT";
						case DbType.Int64: case DbType.UInt64: return "BIGINT";
						default: return "VARCHAR (MAX)";
					}
				
				default: return pDbType.ToString ();
			}
		}
		
		
		public static string GetSQLParamValue (IDbDataParameter pParam)
		{
			if (pParam.Value == null || pParam.Value == DBNull.Value) { return "NULL"; }
		
			switch (pParam.DbType)
			{
				case DbType.Boolean: 
					if (pParam.Value is bool)
					{
						return ((bool) pParam.Value) ? "1" : "0";
					}
					else
					{
						return pParam.Value.ToString ();
					}
			
				case DbType.DateTime: case DbType.DateTime2: case DbType.DateTimeOffset:
					return string.Format ("CONVERT (DATETIME, '{0}', 120)", ((DateTime) pParam.Value).ToString ("yyyy-MM-dd HH:mm:ss"));
					
				case DbType.Byte: case DbType.SByte: 
				case DbType.Int16: case DbType.Int32: case DbType.UInt16: case DbType.UInt32: 
				case DbType.Int64: case DbType.UInt64: return pParam.Value.ToString ();
				
				default: return string.Format ("'{0}'", pParam.Value);
			}
		}
		
		
		public  string GetColumnVectorConcat (DataTable pdtbTable, string pstrColumnName, string pstrSeparador)
		{
			int intI;
		
			StringBuilder stbResultado;
			
		
			if (DataHelper.IsEmpty (pdtbTable)) { return null; }
			
			stbResultado = new StringBuilder (pdtbTable.Rows [0][pstrColumnName].ToString ());
			
			for (intI = 1; intI < pdtbTable.Rows.Count; intI ++)
			{
				stbResultado.AppendFormat ("{0}{1}", pstrSeparador, pdtbTable.Rows [intI][pstrColumnName].ToString ());
			}
		
			return stbResultado.ToString ();
		}
		
		
		/// <summary>
		/// Retorna una seccion de una tabla en otra
		/// </summary>
		/// <param name="pdtbTable">Tabla original con todas las filas</param>
		/// <param name="intIndicePaginaActual">Indice que va desde 1 para la pagina actual</param>
		/// <param name="intTamanoPagina">Tamaño de la pagina, va desde 0</param>
		/// <param name="pintTotalPaginas">Retorna el numero total de paginas disponible</param>
		/// <param name="pblnHayMasPaginas">Retorna un indicador sobre si hay mas paginas mas alla de la actual</param>
		/// <returns>Tabla con las filas de la Tabla original en el intervalo dado, nulo si no hay filas que correspondan</returns>
		public  DataTable GetInterval (DataTable pdtbTable, uint intIndicePaginaActual, uint intTamanoPagina,
			out uint pintTotalPaginas, out uint pintTotalRegistros)
		{
			uint intIndiceInicial;
			uint intIndiceFinal;

			DataTable dtbResultado;


			pintTotalPaginas = 0;
			pintTotalRegistros = 0;

			if (DataHelper.IsEmpty (pdtbTable))
			{	
				return null;
			}
			
			if (intIndicePaginaActual == 0) { intIndicePaginaActual = 1; }

			intIndiceInicial = (intIndicePaginaActual - 1) * intTamanoPagina;

			intIndiceFinal = intIndiceInicial + intTamanoPagina - 1;

			pintTotalRegistros = (uint) pdtbTable.Rows.Count;

			// Si no hay suficientes filas para mostrar la pagina solicitada (limite inferior del intervalo)
			if (intIndiceInicial > 0 && pdtbTable.Rows.Count < intIndiceInicial - 1)
			{
				return null;
			}
			
			// Si no hay suficientes filas para mostrar la pagina solicitada (limite superior del intervalo)
			// Se ajusta el limite superior para permitir extraer la pagina solicitada
			if (pdtbTable.Rows.Count < intIndiceFinal + 1)
			{
				intIndiceFinal = (uint) pdtbTable.Rows.Count - 1;
			}
			
			dtbResultado = pdtbTable.Clone ();
			
			for (; intIndiceInicial <= intIndiceFinal; intIndiceInicial++)
			{
				dtbResultado.ImportRow (pdtbTable.Rows [(int) intIndiceInicial]);
			}

			pintTotalPaginas = (uint) Math.Ceiling ((double) pdtbTable.Rows.Count / (double) intTamanoPagina);

			return dtbResultado;
		}
		
		#endregion


		#region Ejecucion
				
		public static void Confirm (IDbCommand pCommand, string pProviderName, NameValueCollection pPartErrorMsg_ClientMsg = null)
		{
			if (pCommand == null) { return; }

			Log.WriteStartHistory(ToString(pProviderName, pCommand));
			
			try
			{
				Confirm (pProviderName, pCommand.Transaction, pCommand.Connection, pPartErrorMsg_ClientMsg);
			}
			catch (Exception excEx)
			{
				Stop (pCommand, pProviderName, pPartErrorMsg_ClientMsg);
				
				throw new DbGenericException (pProviderName, pCommand, excEx, pPartErrorMsg_ClientMsg);
			}
		}


		public static void Confirm (string pProviderName, IDbTransaction pTransaction, IDbConnection pConnection, NameValueCollection pPartErrorMsg_ClientMsg)
		{
			if (pTransaction != null && pConnection != null && pConnection.State != ConnectionState.Closed)
			{
				try
				{
					pTransaction.Commit ();
				}
				catch (Exception excEx)
				{
					Stop (pProviderName, pTransaction, pConnection, pPartErrorMsg_ClientMsg);
				
					throw new DbGenericException (pProviderName, null, excEx, pPartErrorMsg_ClientMsg);
				}
			}
			
			if (pConnection != null && pConnection.State != ConnectionState.Closed)
			{
				try
				{
					pConnection.Close ();
				}
				catch (Exception excEx)
				{
					throw new DbGenericException (pProviderName, null, excEx, pPartErrorMsg_ClientMsg);
				}
			}
		}



		public static void Stop (IDbCommand pCommand, string pProviderName, NameValueCollection pPartErrorMsg_ClientMsg)
		{
			if (pCommand == null) 
			{
				return;
			}

			try
			{
				Stop (pProviderName, pCommand.Transaction, pCommand.Connection, pPartErrorMsg_ClientMsg);	
			}
			catch (Exception excEx)
			{
				throw new DbGenericException(pProviderName, pCommand, excEx, pPartErrorMsg_ClientMsg);
			}
		}


		public static void Stop (string pProviderName, IDbTransaction pTransaction, IDbConnection pConnection, NameValueCollection pPartErrorMsg_ClientMsg)
		{
			if (pTransaction != null && pTransaction.Connection != null)
			{
				try
				{
					if (pTransaction.Connection.State == ConnectionState.Open)
					{
						pTransaction.Rollback();
					}
				}
				catch (Exception excEx)
				{
					throw new DbGenericException(pProviderName, null, excEx, pPartErrorMsg_ClientMsg);
				}		
			}
			
			if (pConnection != null && pConnection.State == ConnectionState.Open)
			{
				try
				{
					pConnection.Close ();
				}
				catch (Exception excEx)
				{
					throw new DbGenericException(pProviderName, null, excEx, pPartErrorMsg_ClientMsg);
				}
			}
		}

		#endregion
	}
}
